Products export

SELECT p.id_product, pl.name, GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR " & ") as categories, p.price, p.reference, p.quantity,
REPLACE(replace(pl.description_short, "\n", "<br>"), ";", ".") as summary,
REPLACE(replace(pl.description, "\n", "<br>"), ";", ".") as description, pl.meta_title, pl.meta_keywords, pl.meta_description,
GROUP_CONCAT(DISTINCT(CONCAT("position",i.position,":",i.id_image)) SEPARATOR " & ") as images,
GROUP_CONCAT(DISTINCT(CONCAT(fl.name,":",fv.value)) SEPARATOR " & ") as features
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
LEFT JOIN ps_image i ON (p.id_product = i.id_product)
LEFT JOIN ps_feature_product fp ON (p.id_product = fp.id_product)
LEFT JOIN ps_feature_lang fl ON (fp.id_feature = fl.id_feature)
LEFT JOIN ps_feature_value_lang fv ON (fv.id_feature_value = fp.id_feature_value)
WHERE pl.id_lang = 2
AND cl.id_lang = 2
AND fl.id_lang = 2
AND fv.id_lang = 2
AND p.id_shop_default = 1
AND c.id_shop_default = 1
GROUP BY p.id_product;

This creates a dump of the product list with all images, categories and features included.

To create a CSV dump, I go through PHPMyAdmin, paste the query and export the result

All product images are in the img/p folder and subfolders. The tree structure is to create one directory for each picture that hold the original image and the thumbnails. As this create a lot of files to retrieve, I create a link of all original images to a specific folder so that I can only download this folder:

find -name '[0-9][0-9][0-9][0-9].*' -print0 | xargs -n 1 -I {} -0 ln {} originals/
find -name '[0-9][0-9][0-9].*' -print0 | xargs -n 1 -I {} -0 ln {} originals/
find -name '[0-9][0-9].*' -print0 | xargs -n 1 -I {} -0 ln {} originals/
find -name '[0-9].*' -print0 | xargs -n 1 -I {} -0 ln {} originals/